package DBLayer;

import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;

public class DBInvoice implements IFDBInvoice {
	private Connection con;

	public DBInvoice() {
		con = DbConnection.getInstance().getDBcon();
	}

	
	private ArrayList<Invoice> miscWhere(String wClause,
			boolean retrieveAssociation) {
		ResultSet results;
		ArrayList<Invoice> list = new ArrayList<Invoice>();

		String query = buildQuery(wClause);

		try { // read the Invoice from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				Invoice invObj = new Invoice();
				invObj = buildInvoice(results);
				list.add(invObj);
			}// end while
			stmt.close();

		}// slut try
		catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}
	
	private Invoice singleWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;
		Invoice invObj = new Invoice();

		String query = buildQuery(wClause);
		System.out.println(query);
		try { // read the Invoice from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {
				invObj = buildInvoice(results);
				// assocaition is to be build
				stmt.close();

			} else { // no Invoice was found
				invObj = null;
			}
		}// end try
		catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return invObj;
	}

	// method to build the query
	private String buildQuery(String wClause) {
		String query = "SELECT invoiceID, paymentDate, amount,orderID";

		if (wClause.length() > 0)
			query = query + " WHERE " + wClause;

		return query;
	}

	// method to build an Invoice object
	private Invoice buildInvoice(ResultSet results) {
		Invoice invObj = new Invoice();
		try { // the columns from the table emplayee are used
			invObj.setPaymentDate(results.getDate("paymentDate"));
			invObj.setAmount(results.getDouble("amount"));
			invObj.setOrderID(results.getInt("orderID"));

		} catch (Exception e) {
			System.out.println("error in building the Invoice object");
		}
		return invObj;
	}

	@Override
	public ArrayList<Invoice> getAllInvoices(boolean retrieveAssociation) {
		return miscWhere("", retrieveAssociation);
	}

	@Override
	public Invoice findInvoice(int invID, boolean retrieveAssociation) {
		String wClause = " invoiceID = '" + invID + "'";
		return singleWhere(wClause, retrieveAssociation);
				}

	@Override
	public int insertInvoice(Invoice inv) throws Exception {
		int rc = -1;
		String query = "INSERT INTO Invoice(paymentDate,amount,orderID) VALUES('"
				+ inv.getPaymentDate()
				+ "','"
				+ inv.getAmount()
				+ "','"
				+ inv.getOrderID();
		System.out.println("insert : " + query);
		try { // insert new employee + dependent
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// end try
		catch (SQLException ex) {
			System.out.println("Invoice ikke oprettet");
			throw new Exception("Invoice is not inserted correct");
		}
		return (rc);
	}
	

	@Override
	public int updateInvoice(Invoice inv) {
		Invoice invObj = inv;
		int rc = -1;

		String query = "UPDATE invoice SET " + "paymentDate ='" + invObj.getPaymentDate()
				+ "', " + "amount ='" + invObj.getAmount() + "', "
				+ "orderID ='" + invObj.getOrderID() + "'";
		System.out.println("Update query:" + query);
		try { // update invoice
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}// slut try
		catch (Exception ex) {
			System.out.println("Update exception in invoice db: " + ex);
		}
		return (rc);
	}

}
